﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.Xpo;
using BusinessObjects;
using System.Linq;

namespace UI
{
    public partial class frmThietLapChoNam_SinhVien : FormBase
    {
        public frmThietLapChoNam_SinhVien()
        {
            InitializeComponent();
        }

        public int NamThucTapID { get; set; }
        private DataTable _dtLoad;

        private void frmThietLapChoNam_SinhVien_Load(object sender, EventArgs e)
        {
            try
            {
                _dtLoad = new DataTable();
                _dtLoad.Columns.Add("Success", typeof(bool));
                _dtLoad.Columns.Add("Note", typeof(string));
                _dtLoad.Columns.Add("MaSinhVien", typeof(string));
                _dtLoad.Columns.Add("Ho", typeof(string));
                _dtLoad.Columns.Add("Ten", typeof(string));
                _dtLoad.Columns.Add("LopHoc", typeof(string));
                _dtLoad.Columns.Add("NganhHocID", typeof(int));
                _dtLoad.Columns.Add("NganhHoc", typeof(string));
                _dtLoad.Columns.Add("KhoaHoc", typeof(int));
                _dtLoad.Columns.Add("DienThoai", typeof(string));
                _dtLoad.Columns.Add("Email", typeof(string));

                btnExcel_ItemClick(null, null);
            }
            catch (Exception ex)
            {
                CommonError(ex);
            }
        }

        private void btnExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                Dictionary<string,int> dicSinhVien = new Dictionary<string,int>();
                var querySinhVien = Session.DefaultSession.Query<SinhVien>().Select(c => new
                {
                    c.Oid,
                    c.MaSinhVien
                });
                foreach (var item in querySinhVien)
                {
                    dicSinhVien.Add(item.MaSinhVien,item.Oid );
                }
                List<int> lstSinhVienTrongNam = Session.DefaultSession.Query<SinhVienTrongNam>().Where(c => c.NamThucTap.Oid == NamThucTapID).Select(c => c.SinhVien.Oid).ToList();
                Dictionary<string, int> dicNganhHoc = new Dictionary<string, int>();
                var queryNganhHoc = Session.DefaultSession.Query<NganhHoc>().Select(c => new
                {
                    c.Oid,
                    c.MaNganhHoc
                });
                foreach (var item in queryNganhHoc)
                {
                    dicNganhHoc.Add(item.MaNganhHoc, item.Oid);
                }

                OpenFileDialog op = new OpenFileDialog();
                op.Filter = "Excel 2003|*.xls";
                if (op.ShowDialog() == DialogResult.OK)
                {
                    _dtLoad.Rows.Clear();
                    ExcelLibrary.SpreadSheet.Workbook book = ExcelLibrary.SpreadSheet.Workbook.Load(op.FileName);
                    if (book.Worksheets.Count > 0)
                    {
                        ExcelLibrary.SpreadSheet.Worksheet sheet = book.Worksheets[0];
                        for (int row = 1; ; row++)
                        {
                            string strMaSinhVien = string.Empty;
                            if (sheet.Cells[row, 0].Value != null && sheet.Cells[row, 0].Value.ToString() != string.Empty)
                                strMaSinhVien = sheet.Cells[row, 0].Value.ToString();
                            string strHo = string.Empty;
                            if (sheet.Cells[row, 1].Value != null && sheet.Cells[row, 1].Value.ToString() != string.Empty)
                                strHo = sheet.Cells[row, 1].Value.ToString();
                            string strTen = string.Empty;
                            if (sheet.Cells[row, 2].Value != null && sheet.Cells[row, 2].Value.ToString() != string.Empty)
                                strTen = sheet.Cells[row, 2].Value.ToString();
                            string strLopHoc = string.Empty;
                            if (sheet.Cells[row, 3].Value != null && sheet.Cells[row, 3].Value.ToString() != string.Empty)
                                strLopHoc = sheet.Cells[row, 3].Value.ToString();

                            string strNganhHoc = string.Empty;
                            if (sheet.Cells[row, 4].Value != null && sheet.Cells[row, 4].Value.ToString() != string.Empty)
                                strNganhHoc = sheet.Cells[row, 4].Value.ToString();
                            string strKhoaHoc = string.Empty;
                            if (sheet.Cells[row, 5].Value != null && sheet.Cells[row, 5].Value.ToString() != string.Empty)
                                strKhoaHoc = sheet.Cells[row, 5].Value.ToString();
                            string strDienThoai = string.Empty;
                            if (sheet.Cells[row, 6].Value != null && sheet.Cells[row, 6].Value.ToString() != string.Empty)
                                strDienThoai = sheet.Cells[row, 6].Value.ToString();
                            string strEmail = string.Empty;
                            if (sheet.Cells[row, 7].Value != null && sheet.Cells[row, 7].Value.ToString() != string.Empty)
                                strEmail = sheet.Cells[row, 7].Value.ToString();
                            if (strMaSinhVien == string.Empty)
                                break;
                            else
                            {
                                bool isMaSinhVien = true;
                                if (dicSinhVien.ContainsKey(strMaSinhVien))
                                {
                                    if (lstSinhVienTrongNam.Contains(dicSinhVien[strMaSinhVien]))
                                        isMaSinhVien = false;
                                }
                                bool isNganhHoc = dicNganhHoc.ContainsKey(strNganhHoc);
                                bool isKhoaHoc = true;
                                try { int i = Convert.ToInt32(strKhoaHoc); }
                                catch { isKhoaHoc = false; }

                                if (isMaSinhVien && isNganhHoc && isKhoaHoc)
                                {
                                    DataRow dr = _dtLoad.NewRow();
                                    dr["Success"] = true;
                                    dr["Note"] = "Dòng " + (row + 1);
                                    dr["MaSinhVien"] = strMaSinhVien;
                                    dr["Ho"] = strHo;
                                    dr["Ten"] = strTen;
                                    dr["LopHoc"] = strLopHoc;
                                    dr["NganhHocID"] = dicNganhHoc[strNganhHoc];
                                    dr["NganhHoc"] = strNganhHoc;
                                    dr["KhoaHoc"] = strKhoaHoc;
                                    dr["DienThoai"] = strDienThoai;
                                    dr["Email"] = strEmail;
                                    _dtLoad.Rows.Add(dr);
                                }
                                else
                                {
                                    DataRow dr = _dtLoad.NewRow();
                                    dr["Success"] = false;
                                    if (!isMaSinhVien)
                                        dr["Note"] = "Dòng " + (row + 1) + ": Sinh viên đã tồn tại";
                                    else if(!isNganhHoc)
                                        dr["Note"] = "Dòng " + (row + 1) + ": Ngành học không tồn tại";
                                    else if(!isKhoaHoc)
                                        dr["Note"] = "Dòng " + (row + 1) + ": Khóa học nhập sai";
                                    dr["MaSinhVien"] = strMaSinhVien;
                                    dr["Ho"] = strHo;
                                    dr["Ten"] = strTen;
                                    dr["LopHoc"] = strLopHoc;
                                    dr["NganhHoc"] = strNganhHoc;
                                    dr["DienThoai"] = strDienThoai;
                                    dr["Email"] = strEmail;
                                    _dtLoad.Rows.Add(dr);
                                }
                            }
                        }
                    }
                    gvSinhVien.DataSource = _dtLoad;
                }
            }
            catch (Exception ex)
            {
                CommonError(ex);
            }
        }

        private void btnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                DataRow[] drs = _dtLoad.Select("Success=1");
                if (drs.Length > 0)
                {
                    if (CommonConfirm("Bạn muốn lưu dữ liệu này?", "LUU"))
                    {
                        using (NestedUnitOfWork nuow = Session.DefaultSession.BeginNestedUnitOfWork())
                        {
                            List<NganhHoc> lst = nuow.Query<NganhHoc>().ToList();
                            NamThucTap objNamThucTap = nuow.Query<NamThucTap>().FirstOrDefault(c => c.Oid == NamThucTapID);
                            if (objNamThucTap != null)
                            {
                                for (int i = 0; i < drs.Length; i++)
                                {
                                    DataRow dr = drs[i];
                                    string strMaSinhVien = dr["MaSinhVien"].ToString();
                                    var obj = nuow.Query<SinhVien>().FirstOrDefault(c => c.MaSinhVien == strMaSinhVien);
                                    if (obj == null)
                                    {
                                        obj = new SinhVien(nuow);
                                        obj.MaSinhVien = strMaSinhVien;
                                    }
                                    obj.HoSinhVien = dr["Ho"].ToString();
                                    obj.TenSinhVien = dr["Ten"].ToString();
                                    obj.LopHoc = dr["LopHoc"].ToString();
                                    obj.NganhHoc = lst.FirstOrDefault(c => c.Oid == Convert.ToInt32(dr["NganhHocID"]));
                                    obj.KhoaHoc = Convert.ToInt32(dr["KhoaHoc"]);
                                    obj.DienThoai = dr["DienThoai"].ToString();
                                    obj.Email = dr["Email"].ToString();

                                    SinhVienTrongNam objSinhVien = new SinhVienTrongNam(nuow);
                                    objSinhVien.NamThucTap = objNamThucTap;
                                    objSinhVien.SinhVien = obj;
                                }
                                nuow.CommitChanges();
                            }
                        }
                        CommonMessage("Cập nhật thành công");
                        this.DialogResult = DialogResult.OK;
                    }
                }
            }
            catch (Exception ex)
            {
                CommonError(ex);
            }
        }

        private void btnCancel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                this.Close();
            }
            catch (Exception ex)
            {
                CommonError(ex);
            }
        }
    }
}